﻿Imports System.Data
Imports System.Data.SqlClient


Module Module_Stock

    Public Sub Stock_IN(ByVal ItemID As Integer, ByVal Quantity As Integer, ByVal Action As String, ByVal PONo As String)

        Dim str As String
        Dim CurrBalanceQty As Integer
        Dim cmd As New SqlCommand("", gCnnPCE)

        str = "SELECT BalanceQty FROM Items WHERE ID = " & ItemID
        cmd.CommandText = str
        CurrBalanceQty = cmd.ExecuteScalar

        str = "UPDATE Items SET BalanceQTY = " & (Quantity + CurrBalanceQty) & " WHERE ID = " & ItemID
        cmd.CommandText = str
        cmd.ExecuteNonQuery()

        str = "INSERT INTO StockRecords (ItemID, Action, DateTime, UserID, Qty, PONo) VALUES (@ItemID, @Action, @DateTime, @UserID, @Qty, @PONo)"
        cmd.CommandText = str
        cmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = ItemID
        cmd.Parameters.Add("@Action", SqlDbType.NVarChar).Value = Action
        cmd.Parameters.Add("DateTime", SqlDbType.DateTime).Value = Now
        cmd.Parameters.Add("UserID", SqlDbType.Int).Value = CurrUserID
        cmd.Parameters.Add("Qty", SqlDbType.Int).Value = Quantity
        cmd.Parameters.Add("PONo", SqlDbType.NVarChar).Value = PONo

        cmd.ExecuteNonQuery()

    End Sub

    Public Sub Stock_OUT(ByVal ItemID As Integer, ByVal Quantity As Integer, ByVal Action As String)

        Dim str As String
        Dim CurrBalanceQty As Integer
        Dim cmd As New SqlCommand("", gCnnPCE)

        str = "SELECT BalanceQty FROM Items WHERE ID = " & ItemID
        cmd.CommandText = str
        CurrBalanceQty = cmd.ExecuteScalar

        str = "UPDATE Items SET BalanceQTY = " & (CurrBalanceQty - Quantity) & " WHERE ID = " & ItemID
        cmd.CommandText = str
        cmd.ExecuteNonQuery()

        str = "INSERT INTO StockRecords (ItemID, Action, DateTime, UserID, Qty) VALUES (@ItemID, @Action, @DateTime, @UserID, @Qty)"
        cmd.CommandText = str
        cmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = ItemID
        cmd.Parameters.Add("@Action", SqlDbType.NVarChar).Value = Action
        cmd.Parameters.Add("DateTime", SqlDbType.DateTime).Value = Now
        cmd.Parameters.Add("UserID", SqlDbType.Int).Value = CurrUserID
        cmd.Parameters.Add("Qty", SqlDbType.Int).Value = Quantity
        cmd.ExecuteNonQuery()

    End Sub

    Private Sub Insert2ItemsRecords(ByVal InOut As Integer, ByVal ItemID As Integer, ByVal Quantity As Integer)
        Dim str As String
        str = "INSERT INTO ItemsRecords (InOut, ItemID, Quantity) VALUES (@InOut, @ItemID, @Quantity)"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        cmd.Parameters.Add("@InOut", SqlDbType.Int).Value = InOut
        cmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = ItemID
        cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Quantity
        cmd.ExecuteNonQuery()
    End Sub



End Module
